#!/usr/bin/env python
# -*- coding: utf-8 -*-

import logging
import pymysql
from collections import namedtuple
from common.read_base_config import ReadBaseConfig
from pymysql.err import OperationalError, ProgrammingError

logging.basicConfig(level=logging.INFO)
R = ReadBaseConfig()


class MyDB:
    """
    为了增强代码可读性，推荐使用execute_select来执行查询语句
    """

    def __init__(self):
        self.db = None
        self.cursor = None

    @classmethod
    def select_db(cls, db_name):
        database_name = R.get('configuration', 'use_database_config')
        host = R.get(database_name, "host")
        username = R.get(database_name, "username")
        password = R.get(database_name, "password")
        port = R.get(database_name, "port")
        config = {
            'host': host,
            'user': username,
            'passwd': password,
            'port': int(port),
            'db': db_name,
            'charset': 'utf8'
        }
        return config

    def connect_database(self, db_name):
        """
        connect to database
        :return:
        """
        config = self.select_db(db_name)

        try:
            # connect to DB
            self.db = pymysql.connect(**config)
            # create cursor
            self.cursor = self.db.cursor()
        except ConnectionError as ex:
            raise ConnectionError("数据库连接异常！" + str(ex))

    def execute_select(self, db_name, sql_text, is_print_result=True):
        """
        推荐使用这个方法来查询SQL
        :param db_name: 数据库名称
        :param sql_text: 查询语句，只能执行查询
        :param is_print_result: 是否输出查询信息到控制台
        :return: name_tuple列表
        """
        # 转小写
        sql_text_low = sql_text.lower()
        # 分隔字符串，来判断是否是查询语句
        sql_text_strip = sql_text_low.split(' ')
        if "select" == sql_text_strip[0]:
            print("\n执行SQL：", sql_text)
            # 连接数据库
            self.connect_database(db_name)
            # 创建指针
            try:
                self.cursor.execute(sql_text)
            except OperationalError or ProgrammingError:
                raise ValueError('执行的SQL有误，请检查')
            # 字段详情
            des = self.cursor.description
            # 获取所有字段名
            field_list = []
            for item in des:
                if item[0] in ['raise']:
                    field_list.append(item[0] + '_')
                else:
                    field_list.append(item[0])
            if str(field_list).find("(") > 0:
                raise ValueError("查询语句错误，请命名各个字段如：sum(account) as sum_account")

            result = self.cursor.fetchall()

            # 关闭游标与连接
            self.cursor.close()
            self.db.close()
            result = self.list_to_namedtuple(field_list, result)
            if is_print_result:
                print("查询结果：", str(result))
            return result
        else:
            raise ValueError("只能执行查询语句，无法执行删除，更新等操作")

    def execute_sql(self, db_name, sql_text):
        """
        :param db_name:  数据库名称
        :param sql_text:   需要执行的SQL
        :return:
        """
        self.connect_database(db_name)
        # executing sql
        self.cursor.execute(sql_text)
        print("执行SQL：" + sql_text)
        # executing by committing to DB
        self.db.commit()
        return self.cursor

    # def query_sql(self, db_name, sql_test):
    #     logging.info("查询sql：" + sql_test)
    #     self.connect_database(db_name)
    #     cursor = self.db.cursor()
    #     cursor.execute(sql_test)
    #     result = cursor.fetchall()
    #     # 关闭游标与连接
    #     self.cursor.close()
    #     self.db.close()
    #     logging.info("查询数据库结果：" + str(result))
    #     return result

    @staticmethod
    def list_to_namedtuple(field_name_list, list_data):
        """
        :param field_name_list: 字段列表
        :param list_data: 数据列表
        :return:
        """
        if len(list_data) == 0:
            return []
        namedtuple_list = []
        namedtuple_table = namedtuple("select_info", field_name_list)

        for t in list_data:
            namedtuple_one = namedtuple_table(*t)
            namedtuple_list.append(namedtuple_one)
        return namedtuple_list

    def execute_update(self, db_name, sql_text):
        """
        推荐使用这个方法来查询SQL
        :param db_name: 数据库名称
        :param sql_text: 查询语句，update
        """
        # 转小写
        sql_text_low = sql_text.lower()
        # 分隔字符串，来判断是否是更新语句
        sql_text_strip = sql_text_low.split(' ')

        if "update" == sql_text_strip[0] and 'set' == sql_text_strip[2]:
            print("执行SQL：", sql_text)
            # 连接数据库
            self.connect_database(db_name)
            # 创建指针
            result = self.cursor.execute(sql_text)
            print("更新了【%s】条记录！" % str(result))
            # 提交更新
            self.db.commit()

            return result
        else:
            raise ValueError("只能执行查询语句，无法执行删除，更新等操作")

    def execute_alter(self, db_name, sql_text):
        """
        :param db_name: 数据库名称
        :param sql_text: 查询语句，update
        """
        # 转小写
        sql_text_low = sql_text.lower()
        # 分隔字符串，来判断是否是更新语句
        # print("执行SQL：", sql_text)
        # 连接数据库
        self.connect_database(db_name)
        # 创建指针
        result = self.cursor.execute(sql_text)
        # print("更新了【%s】条记录！" % str(result))
        # 提交更新
        self.db.commit()
        self.db.close()
        return result

    def async_sql(self, db_name, sql_text):
        """
        异步事务测试专用
        :param db_name: 数据库名称
        :param sql_text: 查询语句，只能执行查询
        """
        print("执行SQL：", sql_text)
        # 连接数据库
        self.connect_database(db_name)
        # 创建指针
        self.cursor.execute(sql_text)
        result = self.cursor.fetchall()
        self.db.commit()
        return result

    def async_for_update(self, db_name, sql_text):
        """
        异步事务测试专用
        :param db_name: 数据库名称
        :param sql_text: 查询语句，只能执行查询
        """
        print("执行SQL：", sql_text)
        # 连接数据库
        self.connect_database(db_name)
        # 创建指针
        self.cursor.execute(sql_text)
        result = self.cursor.fetchall()

        return result


if __name__ == '__main__':
    test = MyDB()
    sql = "select * FROM common_coin_type"
    # 执行查询操作，返回列表 [select_info(),select_info(),select_info(),select_info()]
    list_a = test.execute_select("coin", sql)
    print(list_a)
    # 取列表的第一项
    # b = list_a[0]
    # 输出type_name的值
    # print(b.type_name)
    # sql = "update common_coin_credits " \
    #       "set version=version+1 " \
    #       "where  member_id = '1490' and account_type = '1' and investment_id = '0'"
    #
    # a = test.execute_update('coin', sql)

    # test.async_sql(
    #     db_name='finance',
    #     sql_text='lock tables finance_account_log read local'
    # )
    # from time import sleep
    #
    # sleep(500000)
